'use strict';

const Service = require('egg').Service;
const moment = require('moment');
/**
 * 人员去向表服务类
 */
class DestinationService extends Service {
  /**
   * 根据手机号和密码查询用户
   * @param {string} phoneNumber - 手机号
   * @param {string} password - 密码
   * @returns {Promise<Object|null>} 用户信息，不存在时返回null
   */
  async findByPhoneAndPassword(phoneNumber, password) {
    const result = await this.app.mysql.query(
      'SELECT * FROM tb_person WHERE phone_number = ? AND password = ? AND is_deleted = 0 AND is_actived = 1',
      [phoneNumber, password]
    );
    return result[0];
  }

  /**
   * 获取人员去向表

   * @param {string} year_month - 年月
   * @returns {Promise<Object|null>} 人员去向表
   */
  async getPersonDestinations({
    year_month
  }) {
    // 参数校验
    if (year_month && !moment(year_month, 'YYYY-MM', true).isValid()) {
      ctx.throw(500, 'Invalid year_month format. It should be in YYYY-MM format.');
    }
    let sql = `
        SELECT
          p.person_name AS 'person_name',
          p.person_id AS 'person_id',
          p.position AS 'position',
          d.department_name AS 'department_name',
          d.department_id AS 'department_id',-- 构建动态的日期列查询部分，用于按年月筛选
          ${this.buildDateColumns(year_month)}
        FROM
          tb_person p
          LEFT JOIN tb_department d ON p.department_id = d.department_id
          LEFT JOIN person_destination pd ON p.person_id = pd.person_id 
          AND pd.specific_date BETWEEN ? 
          AND ? 
        WHERE
	        p.is_deleted = 0 -- 筛选未被删除的人员
	        AND ( d.is_deleted = 0 OR d.department_id IS NULL ) -- 筛选未被删除的部门
        GROUP BY
          p.person_id,
          p.person_name,
          p.position,
          d.department_name,
          d.department_id 
        ORDER BY
          p.person_id;`;
    const params = [];

    if (year_month) {
      const year = year_month.slice(0, 4);
      const month = year_month.slice(5);
      const startDate = `${year}-${month}-01`;
      const endDate = `${year}-${month}-${new Date(year, month - 1, 0).getDate()}`;
      params.push(startDate, endDate);
    }
    try {
      const result = await this.app.mysql.query(sql, params);
      return result;
    } catch (error) {
      // 记录错误日志，这里简单打印，实际可集成日志系统
      console.error('Error in getPersonDestinations:', error);
      ctx.throw(500, error);
    }
  }

  buildDateColumns(yearMonth) {
    const start = moment(yearMonth, 'YYYY-MM');
    const end = start.clone().endOf('month');
    const days = [];
    let current = start;
    while (current.isSameOrBefore(end)) {
      days.push(`MAX(CASE WHEN pd.specific_date = '${current.format('YYYY-MM-DD')}' THEN pd.destination END) AS '${current.format('YYYY-MM-DD')}'`);
      current = current.add(1, 'day');
    }
    return days.join(',\n');
  }

  /**
   *  新增 / 更新 / 删除人员去向信息接口（统一处理）
   * @param {number} person_id - 人员id
   * @param {string} specific_date - YYYY-MM-dd
   * @param {string} destination  - 去向
   * @returns {Promise<Object|null>}
   */
  async handlePersonDestination({
    person_id,
    specific_date,
    destination
  }) {
    // 参数校验
    if (!person_id) {
      throw new Error('person_id is required.');
    }
    if (!moment(specific_date, 'YYYY-MM-DD', true).isValid()) {
      throw new Error('Invalid specific_date format. It should be in YYYY-MM-DD format.');
    }

    if (destination === null || destination === '') {
      // 删除操作
      const sql = `DELETE FROM person_destination WHERE person_id =? AND specific_date =?`;
      try {
        const result = await this.app.mysql.query(sql, [person_id, specific_date]);
        return result;
      } catch (error) {
        // 记录错误日志，这里简单打印，实际可集成日志系统
        console.error('Error in delete operation:', error);
        ctx.throw(500, error);
      }
    } else {
      // 新增/更新操作
      const querySql = `SELECT destination_id FROM person_destination WHERE person_id =? AND specific_date =?`;
      try {
        const queryResult = await this.app.mysql.query(querySql, [person_id, specific_date]);
        if (queryResult.length > 0) {
          const updateSql = `UPDATE person_destination SET destination =? WHERE person_id =? AND specific_date =?`;
          const result = await this.app.mysql.query(updateSql, [destination, person_id, specific_date]);
          return result;
        } else {
          const insertSql = `INSERT INTO person_destination (person_id, specific_date, destination, created_at) VALUES (?,?,?,${this.app.mysql.literals.now})`;
          const result = await this.app.mysql.query(insertSql, [person_id, specific_date, destination]);
          return result;
        }
      } catch (error) {
        // 记录错误日志，这里简单打印，实际可集成日志系统
        console.error('Error in insert/update operation:', error);
        ctx.throw(500, error);
      }
    }
  }

  /**
   *  查询 某人 某天 的去向
   * @param {number} person_id - 人员id
   * @param {string} specific_date - YYYY-MM-dd
   * @returns {Promise<Object|null>}
   */
  async handlePersonDestinationByPidAndDate({
    person_id,
    specific_date,
  }) {
    // 参数校验
    if (!person_id) {
      throw new Error('person_id is required.');
    }
    if (!moment(specific_date, 'YYYY-MM-DD', true).isValid()) {
      throw new Error('Invalid specific_date format. It should be in YYYY-MM-DD format.');
    }

    try {
      const querySql = `SELECT * FROM person_destination WHERE person_id =? AND specific_date =?`;
      const queryResult = await this.app.mysql.query(querySql, [person_id, specific_date]);
      return queryResult;
    } catch (error) {
      // 记录错误日志，这里简单打印，实际可集成日志系统
      console.error('Error in insert/update operation:', error);
      ctx.throw(500, error);
    }
  }

  /**
   *  查询 某人 所有去向
   * @param {number} person_id - 人员id
   * @returns {Promise<Object|null>}
   */
  async handlePersonDestinationByPid({
    person_id
  }) {
    // 参数校验
    if (!person_id) {
      throw new Error('person_id is required.');
    }

    try {
      const querySql = `SELECT * FROM person_destination WHERE person_id =?`;
      const queryResult = await this.app.mysql.query(querySql, [person_id]);
      return queryResult;
    } catch (error) {
      // 记录错误日志，这里简单打印，实际可集成日志系统
      console.error('Error in insert/update operation:', error);
      ctx.throw(500, error);
    }
  }

  /**
   *  查询 某人 所有投稿
   * @param {number} person_id - 人员id
   * @returns {Promise<Object|null>}
   */
  async handlePersonContributionByPid({
    person_id,
  }) {
    // 参数校验
    if (!person_id) {
      throw new Error('person_id is required.');
    }

    try {
      const querySql = `
        SELECT 
            c.title,
            c.submission_date
        FROM 
            tb_contribution c
        WHERE 
            -- 筛选出审核通过的投稿
            c.approved_status = 1
            -- 检查该人员是否为作者 1
            AND (c.author1 = ${person_id}
            -- 检查该人员是否为作者 2
            OR c.author2 = ${person_id}
            -- 检查该人员是否为作者 3
            OR c.author3 = ${person_id})
            -- 确保投稿记录未被删除
            AND c.isDeleted = 0`;
      const queryResult = await this.app.mysql.query(querySql);
      return queryResult;
    } catch (error) {
      // 记录错误日志，这里简单打印，实际可集成日志系统
      console.error('Error in insert/update operation:', error);
      ctx.throw(500, error);
    }
  }

}

module.exports = DestinationService;